#!usr/bin/env python
# -*- coding:utf-8 _*-
"""
@author:xaoyaoyao
@file: mysql_twisted_pipeline.py
@time: 2018/08/19
"""
import sys
import pymysql
import datetime
import logging
from twisted.enterprise import adbapi
from article_spider.items import CommentItem
from article_spider.utils.tools import Tools

if sys.getdefaultencoding() != 'utf-8':
    sys.setdefaultencoding('utf-8')


class MysqlTwistedPipeline(object):
    def __init__(self, dbpool):
        self.dbpool = dbpool

    # 从配置中获取信息
    @classmethod
    def from_settings(cls, settings):
        dbparms = dict(
                host=settings['MYSQL_HOST'],
                db=settings['MYSQL_DB'],
                user=settings['MYSQL_USER'],
                passwd=settings['MYSQL_PWD'],
                charset='utf8',
                cp_min=5,
                cp_max=30,
                cursorclass=pymysql.cursors.DictCursor,
                use_unicode=True
        )
        dbpool = adbapi.ConnectionPool("pymysql", **dbparms)
        return cls(dbpool)

    def process_item(self, item, spider):
        try:
            # 使用twisted将mysql插入编程异步执行
            # 第一个参数是我们定义的函数
            if type(item) == CommentItem:
                self.dbpool.runInteraction(self.do_comment_insert, item)
            else:
                self.dbpool.runInteraction(self.do_insert, item)
        except Exception as e:
            logging.error("Insert Item error. The msg %s", str(e))
        return item

    def do_comment_insert(self, cursor, item):
        _now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        _url = item['url']
        _title = item['title']
        _content = item['content']
        _type = None
        _author = None
        _read = None
        _domain = None
        _brief = None
        _review = None
        _date = None
        _grade = None
        _name = None
        comments = None
        _cata = None
        if 'author' in item.keys():
            _author = item['author']
        if 'read' in item.keys():
            _read = item['read']
        if 'domain' in item.keys():
            _domain = item['domain']
        if 'review' in item.keys():
            _review = item['review']
        if 'date' in item.keys():
            _date = item['date']
        if 'brief' in item.keys():
            _brief = item['brief']
        if 'type' in item.keys():
            _type = item['type']
        if 'grade' in item.keys():
            _grade = item['grade']
        if 'name' in item.keys():
            _name = item['name']
        if 'comments' in item.keys():
            comments = item['comments']
        if 'cata' in item.keys():
            _cata = item['cata']
        sql = 'SELECT * FROM ai_spider_article WHERE url = %s'
        cursor.execute(sql, _url)
        # 获取查询结果
        result = cursor.fetchone()
        if not result:
            _data = (
                _name, _type, _grade, _date, _url, _title, _content, 1, _now, _author, _read, _domain, _brief, _review, _cata)
            cursor.execute(
                    "INSERT INTO ai_spider_article (name, type, grade, date, url, title, content, status, create_date, author, read_count, domain, brief, review, cata) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                    _data)
        if comments and len(comments) > 0:
            cursor.execute(sql, _url)
            # 获取查询结果
            result = cursor.fetchone()
            if result:
                values = []
                for i in range(len(comments)):
                    ele = comments[i]
                    score= None
                    if isinstance(ele, str):
                        content = ele
                    else:
                        content = ele['content']
                        score = ele['score']

                    _data = (result['id'], content, _now, score)
                    values.append(_data)
                cursor.executemany(
                        "INSERT INTO ai_spider_comment (article_id, content, create_date, score) VALUES (%s, %s, %s, %s)", values)

    def do_insert(self, cursor, item):
        _now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        _name = item['name']
        _type = item['type']
        _grade = item['grade']
        _url = item['url']
        _title = item['title']
        _content = item['content']
        _author = None
        _read = None
        _domain = None
        _brief = None
        _review = None
        _date = None
        _cata = None
        _score = None
        if 'author' in item.keys():
            _author = item['author']
        if 'read' in item.keys():
            _read = item['read']
        if 'domain' in item.keys():
            _domain = item['domain']
        if 'review' in item.keys():
            _review = item['review']
        if 'date' in item.keys():
            _date = item['date']
        if 'brief' in item.keys():
            _brief = item['brief']
        if 'cata' in item.keys():
            _cata = item['cata']
        if _author:
            _author = Tools.author_filter(_author)
        if _content and len(str(_content)) > 5:
            _content = Tools.content_filter(_content)
            _data = (
                _name, _type, _grade, _date, _url, _title, _content, 1, _now, _author, _read, _domain, _brief, _review, _cata)
            cursor.execute(
                    "INSERT INTO sw_spider_article (name, type, grade, date, url, title, content, status, create_date, author, read_count, domain, brief, review, cata) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                    _data)
